package cn.zhl.boot.service;

import cn.zhl.boot.util.ColumnTypeMap;
import org.dom4j.Document;
import org.dom4j.DocumentHelper;
import org.dom4j.Element;
import org.springframework.stereotype.Service;

import java.io.BufferedWriter;
import java.io.File;
import java.io.FileWriter;
import java.util.ArrayList;
import java.util.HashSet;
import java.util.List;
import java.util.Set;

/**
 * @program: demos
 * @description:
 * @author: 刘振华
 * @create: 2020-06-06 12:17
 **/
@Service
public class SqlService {
	public void printSql(String tableName,String xml){
		Document doc = null;
		List<FileWriter> fws = new ArrayList<>();
		List<BufferedWriter> bws = new ArrayList<>();
		try {
			doc = DocumentHelper.parseText(xml); // 将字符串转为XML
			Element rootElt = doc.getRootElement(); // 获取根节点
			FileWriter fileWriter = null;
			BufferedWriter bw = null;
			String insertBegin = " INTO "+tableName+"( ";
			String insertMid = " ) VALUES ( ";
			String insertEnd = " )";
			Set<String> set = new HashSet<>();
			List<Element> list = rootElt.elements();
			for(int i = 0;i<list.size();i++){
				Element table = list.get(i);
				if(i%1000 == 0){
					File file =new File(tableName+(i/1000)+".sql");
					if(file.exists()){
						file.delete();
					}
					file.createNewFile();
					//使用true，即进行append file
					fileWriter = new FileWriter(file.getName(),true);
					fws.add(fileWriter);
					bw = new BufferedWriter(fileWriter);
					bw.write("INSERT ALL");
					bw.newLine();
					bws.add(bw);
				}

				List<Element> nameAndDatas = table.elements();
				String fields = "";
				String values = "";
				for(Element ele : nameAndDatas){
					String name = ele.getName();
					fields += name+",";
					String s = ColumnTypeMap.map.get(tableName+":"+name);
					if(s==null){
						set.add(tableName+":"+name);
						continue;
					}
					if(s.startsWith("NUMERIC")) {
						values += "" + ele.getData().toString() + ",";
					}else if(s.startsWith("DATE")) {
						values += "to_date('" + ele.getData().toString() + "' , 'yyyy/mm/dd hh24:mi:ss'),";
					}else{
						values += "'" + ele.getData().toString().trim() + "',";
					}
				}
				bw.write(insertBegin);
				bw.write(fields.substring(0,fields.length()-1));
				bw.write(insertMid);
				bw.write(values.substring(0,values.length()-1));
				bw.write(insertEnd);
				bw.newLine();
				if((i+1)%1000 == 0){
					bw.write("SELECT 1 FROM DUAL;");
					bw.flush();
				}
			}
			bw.write("SELECT 1 FROM DUAL;");
			System.out.println(set);
		} catch (Exception e) {
			e.printStackTrace();
		}finally {
			try {
				for(BufferedWriter bw : bws){
					bw.close();
				}
//				if(fileWriter != null) {
//					fileWriter.flush();
//					fileWriter.close();
//				}
			}catch (Exception e){
				e.printStackTrace();
			}
		}
	}
}
